<?php

namespace App\Http\Controllers;


use App\Model\Device;
use App\Model\InverterData;
use App\Model\InverterDataThree;
use App\Model\InverterDataTool;
use App\Model\InverterDataTwo;
use App\Model\RecordDevicesDayGenCap;
use App\Model\Site;
use App\Model\StatisticsDayEnergy;
use App\Model\StatisticsDeviceDayEnergy;
use App\Model\StatisticsDeviceYearEnergy;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;
use Redis;

class TestController extends Controller
{


    public function getReday()
    {
//        $begin = date('Y-m-d') . '23:59:59';
        $begin = "2020-10-19 19:59:59";
        $table = ['one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight',
            'nine', 'ten', 'eleven', 'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen', 'twenty', 'twentyone', 'twentytwo', 'twentythree', 'twentyfour', 'twentyfive', 'twentysix', 'twentyseven', 'twentyeight', 'twentynine', 'thirty', 'thirtyone'];

        $month = date('m');
        $year = date('Y');
//        $sites =  Site::whereIn('status',[1,2])->get();
        $sites = [1, 3, 5, 7, 9, 10, 11, 14, 15, 16, 17, 18, 19, 20, 21, 22, 25, 27, 28, 29, 31, 33,34,35, 37, 38,41, 43, 44, 45,46, 47, 48, 50, 51, 52, 54, 55,57, 58, 59,60,61];
//        $sites = [60,61];
//        $sites = [34,41,46,55];
        $filed = intval(date('d', strtotime($begin))) - 1;
//        $filed = 15;
        $index = $table[$filed];
        dump($index);
        $results = [];
        foreach ($sites as $site) {
//            $id = $site->id;
            $id = $site;
            $devices = Device::where('site_id', $id)->get(['device_id']);
            switch ($id) {
                case $id < 18:
                    foreach ($devices as $device) {
                        $device_id = $device->device_id;
                        $obj = InverterData::where('site_id', $id)->where('device_id', $device_id)->where('DayGenCap', '<>', 0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();
                        if ($obj) {
                            if (date('Y-m-d', strtotime($obj->EventTime)) == date('Y-m-d')) {
                                $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                            } else {
                                $device_info = ['site_id' =>$id, 'device_id' => $device_id, $index => 0];
                            }
//                            $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                        } else {
                            $device_info = ['site_id' =>$id, 'device_id' => $device_id, $index => 0];
                        }
//                        $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                        array_push($results, $device_info);
                    }
                    break;
                case $id >= 18 && $id < 43:
                    foreach ($devices as $device) {
                        $device_id = $device->device_id;
                        $obj = InverterDataTwo::where('site_id', $id)->where('device_id', $device_id)->where('DayGenCap', '<>', 0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();
                        if ($obj) {
                            if (date('Y-m-d', strtotime($obj->EventTime)) == date('Y-m-d')) {
                                $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                            } else {
                                $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => 0];
                            }
//                            $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => $obj->DayGenCap];

                        } else {
                            $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => 0];
                        }
//                        $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                        array_push($results, $device_info);
                    }
                    break;
                default:
                    foreach ($devices as $device) {
                        $device_id = $device->device_id;
                        $obj = InverterDataThree::where('site_id', $id)->where('device_id', $device_id)->where('DayGenCap', '<>', 0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();

                        if ($obj) {
                            if (date('Y-m-d', strtotime($obj->EventTime)) == date('Y-m-d')) {
                                $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                            } else {
                                $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => 0];
                            }
//                            $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                        } else {
                            $device_info = ['site_id' => $id, 'device_id' => $device_id, $index => 0];
                        }
//                        $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                        array_push($results, $device_info);
                    }
            }
//            DB::beginTransaction();
//            dump($results);
            foreach ($results as $result) {
                $param['site_id'] = $result['site_id'];
                $param['device_id'] = $result['device_id'];
                $param[$index] = $result[$index];
                $param['month'] = $month;
                $param['year'] = $year;

                $record = RecordDevicesDayGenCap::where('site_id', $result['site_id'])
                    ->where('device_id', $result['device_id'])
                    ->where('month', $month)
                    ->where('year', $year)
                    ->first();
                if ($record) {
                    dump("up");
                    dump($param);
//                    $record->update($param);
                } else {
                    if ($result[$index] != 0) {
//                        RecordDevicesDayGenCap::create($param);
                        dump("up");
                        dump($param);
                    }
                }
            }
//            DB::commit();
        }
    }




    public function getPublish(Request $request){
        $id = $request->input("s_id");
        $devices = Device::where('site_id',$id)->get(['device_id']);
        $begin = "2020-10-16 19:59:59";
        $results = [];
        foreach ($devices as $device) {
            $device_id = $device->device_id;
            if ($id < 18) {
                $obj = InverterData::where('site_id', $id)->where('device_id', $device_id)->where('DayGenCap', '<>', 0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();
            }
            if ($id >=18 &$id < 43){
                $obj = InverterDataTwo::where('site_id', $id)->where('device_id', $device_id)->where('DayGenCap', '<>', 0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();
            }

            if ($id >= 43){
                $obj = InverterDataThree::where('site_id', $id)->where('device_id', $device_id)->where('DayGenCap', '<>', 0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();
            }
            if ($obj) {
                $device_info = ['site_id' => $id, 'device_id' => $device_id, 'sixteen' => $obj->DayGenCap];
            }else{
                $device_info = ['site_id' => $id, 'device_id' => $device_id, 'sixteen' => 0];
            }
            array_push($results,$device_info);
        }

        foreach ($results as $result){
            $param['site_id'] = $result['site_id'];
            $param['device_id'] = $result['device_id'];
            $param['sixteen'] = $result['sixteen'];
            $param['month'] = '10';
            $param['year'] = '2020';
            $record = RecordDevicesDayGenCap::where('site_id', $result['site_id'])
                ->where('device_id', $result['device_id'])
                ->where('month','10')
                ->where('year','2020')
                ->first();
            if($record){
                dump("->update");
                dump($param);
                $record->update($param);
            }else{
                if($result['sixteen'] != 0) {
                    dump("->new");
                    dump($param);
                    RecordDevicesDayGenCap::create($param);
                }
            }
        }
    }

    public function getRecordYearData(){
//        $begin_time = date('Y-m-d') . ' 0:0:0';
        $begin_time = '2019-12-31 00:00:00';
//        $end_time = date('Y-m-d') . ' 23:59:59';
        $end_time = '2019-12-31 23:59:59';
        $devices = Device::where('site_id',3)->get();

        foreach ($devices  as $device) {
//            if($device->site_id < 18) {
//                $data = InverterData::where('device_id', $device->id)
//                    ->where('site_id',$device->site_id)
//                    ->whereBetween('EventTime', [$begin_time, $end_time])
//                    ->orderBy('id', 'desc')->first();
//            }else{
//                $data = InverterDataTwo::where('device_id', $device->id)
//                    ->where('site_id',$device->site_id)
//                    ->whereBetween('EventTime', [$begin_time, $end_time])
//                    ->orderBy('id', 'desc')->first();
//            }
            $site_id = $device->site_id;
            $device_id = $device->device_id;
            dump($device->device_id);
            dump($device->site_id);
            if($site_id < 18){
                $data = InverterData::where('device_id', $device->id)
                    ->where('site_id',$device->site_id)
//                        ->whereBetween('EventTime', [$begin_time, $end_time])
                    ->orderBy('id', 'desc')->first();
                dump($data);
            }
            if($site_id>=18&&$site_id<43){
                $data = InverterDataTwo::where('device_id', $device->id)
                    ->where('site_id',$device->site_id)
//                        ->whereBetween('EventTime', [$begin_time, $end_time])
                    ->orderBy('id', 'desc')->first();
            }
            if($site_id>=43){
                $data = InverterDataThree::where('device_id', $device->id)
                    ->where('site_id',$device->site_id)
//                        ->whereBetween('EventTime', [$begin_time, $end_time])
                    ->orderBy('id', 'desc')->first();
            }


//            switch ($site_id){
//                case $site_id < 18:
//                    $data = InverterData::where('device_id', $device->id)
//                        ->where('site_id',$device->site_id)
////                        ->whereBetween('EventTime', [$begin_time, $end_time])
//                        ->orderBy('id', 'desc')->first();
//                    break;
//                case $site_id>=18&&$site_id<43:
//                    $data = InverterDataTwo::where('device_id', $device->id)
//                        ->where('site_id',$device->site_id)
////                        ->whereBetween('EventTime', [$begin_time, $end_time])
//                        ->orderBy('id', 'desc')->first();
//                    break;
//                default:
//                    $data = InverterDataThree::where('device_id', $device->id)
//                        ->where('site_id',$device->site_id)
////                        ->whereBetween('EventTime', [$begin_time, $end_time])
//                        ->orderBy('id', 'desc')->first();
////                    break;
////                default:
////                    $data = InverterData::where('device_id', $device->id)
////                        ->where('site_id',$device->site_id)
////                        ->whereBetween('EventTime', [$begin_time, $end_time])
////                        ->orderBy('id', 'desc')->first();
//            }
                if ($data) {
//                    $obj = StatisticsDeviceYearEnergy::where('site_id', $device->site_id)->where('device_id', $device->id)->where('date', '2019')->first();
//                    if (!$obj) {
                        dump($device->site_id);
                        dump($device->user_id);
                        dump($device->device_id);
                        dump($data->TotGenCap);
                       $re= StatisticsDeviceYearEnergy::create(['site_id' => $device->site_id, 'user_id' => $device->user_id, 'device_id' => $device->device_id, 'value' => $data->TotGenCap, 'date' => '2019']);
                       dump($re);
//                    }
                }

        }
    }





    public function getResultJson(){
//
        $result = ["status_code"=>2000,"status_desc"=>"","data"=>['detail'=>[['1'=>2],['1'=>2],['1'=>2]],'total'=>['1'=>2]]];
        $json = json_encode($result);
//        $sites = Site::all();
//        $ar = $sites->toArray();
//        $json = json_encode($ar);
        return $json;
    }

    public function getTestForCron(Request $request){

        $date = '2019-03-17';
        DB::beginTransaction();
        $devices = Device::where('site_id',7)->orderBy('site_id')->get();
        foreach ($devices as $device) {
            $sdde = StatisticsDeviceDayEnergy::getOneDayByDeviceId($device->user_id, $device->device_id, $date);
            if (!$sdde) {
                $param['device_id'] = $device->device_id;
                $param['user_id'] = $device->user_id;
                $param['site_id'] = $device->site_id;
                $param['date'] = $date;
                $site_id = $device->site_id;

                if ($site_id < 18) {
                    $param['value'] = InverterData::getOneDayGenCapByDeviceId($device->user_id, $device->device_id, $date);
                }
                if ($site_id >= 18 && $site_id < 43) {
                    $param['value'] = InverterDataTwo::getOneDayGenCapByDeviceId($device->user_id, $device->device_id, $date);
                }
                if ($site_id >= 43) {
                    $param['value'] = InverterDataThree::getOneDayGenCapByDeviceId($device->user_id, $device->device_id, $date);
                }
               $obj = StatisticsDeviceDayEnergy::create($param);
                if(!$obj){
                    DB::rollback();
                }
            }
        }
        DB::commit();

    }



    public function getTest(){
        $cellData = [
            ['学号','姓名','成绩'],
            ['10001','AAAAA','99'],
            ['10002','BBBBB','92'],
            ['10003','CCCCC','95'],
            ['10004','DDDDD','89'],
            ['10005','EEEEE','96'],
        ];
        $user = $this->_getCurrentUser();
        $begin = date('Y-m-d H:i:s',strtotime('-2 hour'));
        $end = date('Y-m-d H:i:s');
        $site = Site::where('user_id',5)->first();
        $site_name = $site?$site->name:'';
        $objs = InverterData::where('user_id',5)
            ->whereBetween('EventTime',[$begin,$end])
            ->orderBy('device_id','desc')
            ->orderBy('EventTime','desc')
            ->limit(1000)
            ->get();
        foreach($objs as $obj){
            unset($obj->user_id);
            unset($obj->site_id);
            unset($obj->created_at);
            unset($obj->updated_at);
            unset($obj->deleted_at);
        }
        Excel::create(date('Y-m-d').'发电数据',function($excel) use ($objs,$site_name){
            $excel->sheet($site_name, function($sheet) use ($objs){
                $sheet->fromModel($objs, false, false, false, false);
                $sheet->prependRow(array('id','device_id','DCU1','DCI1','DCU2','DCI2','DCU3','DCI3','DCU4','DCI4','ACUA','ACIA','ACUB','ACIB','ACUC','ACIC','IVTemp','TotGenCap','IVStatus','IVError','IVMode','FR','IVRun','YearGenCap','MonthGenCap','LastMonthGenCap','DayGenCap','LastDayGenCap','PowerLimitPercent','ActivePower','ReactivePower','ApparentPower','PowerFactor','BoardTemp1','BoardTemp2','IVType','EventTime','EventType'));
                $sheet->prependRow(array('字段','设备编号','直流电压1','直流电流1','直流电压2','直流电压2','直流电压3','直流电流3','直流电压4','直流电流4','交流A相电压','交流A相电流','交流B相电压','交流B相电流','交流C相电压','交流C相电流','逆变器温度','TotGenCap','逆变器状态','IVError','IVMode','电网频率','IVRun','年发','月发','上个月发','日发','昨天发','PowerLimitPercent','有功功率','无功功率','视在功率','功率因素','板测温度1','板测温度2','IVType','EventTime','EventType'));
            });
        })->export('xls');
        dump(1);
    }

    public function getTestTwo(){

//        $date = date('Y-m-d');//获取当天
        $date = '2017-06-27';
        $devices = Device::get();
        foreach ($devices as $device) {
            $sdde = StatisticsDeviceDayEnergy::getOneDayByDeviceId($device->user_id,$device->device_id, $date);
            if (!$sdde) {
                $sdde = new StatisticsDeviceDayEnergy();
                $sdde->device_id = $device->device_id;
                $sdde->user_id = $device->user_id;
                $sdde->site_id = $device->site_id;
                $sdde->date = $date;
                $sdde->value = InverterData::getOneDayGenCapByDeviceId($device->user_id,$device->device_id, $date);
                $sdde->save();
            }
        }
    }

    public function getTestThree(){
        $redis = $this->_Redis();
        $id = 21;
        $key = 'xy:industry:data:' . $id . ':*';
        $keys = $redis->keys($key);
        $pipe = $redis->multi(Redis::PIPELINE);
        foreach ($keys as $k) {
            $pipe->get($k);
        }
        $results = $pipe->exec();
        foreach ($results as $result) {
            if ($result) {
                $arr = json_decode($result, true);
                if (date('Y-m-d', strtotime($arr['EventTime'])) == date('Y-m-d')) {
                    if (time() - strtotime($arr['EventTime']) < 3600) {
                        if ($arr['DayGenCap'] >= 0 && $arr['DayGenCap'] < 10000) {
                            array_push($data, $arr['DayGenCap']);
                        }
                    } else {
                        array_push($lost_data, $arr['DayGenCap']);
                    }
                }
            }
        }

    }

    public function getCountYearData(){
        $redis = $this->_Redis();
        $sites =  Site::all();
        $ids = [];
        $gen_cap = [];
        $month_gen_cap = [];
        $day_gen_cap = [];
        $data = [];
        foreach ($sites as $site){
            array_push($ids,$site->id);
        }
        foreach ($ids as $id){
            $key = 'xy:industry:data:' . $id . ':*';
            $keys = $redis->keys($key);
            $pipe = $redis->multi(Redis::PIPELINE);
            foreach ($keys as $k) {
                $pipe->get($k);
            }
            $results = $pipe->exec();
            $sum = [];
            $day_sum = [];
            $month_sum = [];
            foreach ($results as $result) {
                if ($result) {
                    $arr = json_decode($result, true);
//                    array_push($data, ['site_id'=>$arr['site_id'],'user_id'=>$arr['user_id'],'device_id'=>$arr['device_id'],
//                        'value'=>$arr['TotGenCap'],'date'=>'2018']);
                    array_push($sum, $arr['TotGenCap']);
                    array_push($month_sum, $arr['MonthGenCap']);
                    array_push($day_sum, $arr['DayGenCap']);
                }
            }
            $value = collect($sum)->sum();
            $month_value = collect($month_sum)->sum();
            $day_value = collect($day_sum)->sum();
            $gen_cap[$id] = $value;
            $month_gen_cap[$id] = $month_value;
            $day_gen_cap[$id] = $day_value;

        }

        dump($gen_cap);
        dump($month_gen_cap);
        dump($day_gen_cap);
//        $i = DB::table('statistics_device_year_energies')->insert($data);
//        dump($gen_cap);
//        dump($i);
//        foreach ($gen_cap as $k=>$v) {
//            dump($k);
//            dump($v);
////            $d = DB::table('statistic_sum')->insert(['site_id'=>$k,'value'=>$v]);
////            dump($d);
//        }
    }

    public function getDataList(){
        $redis = $this->_Redis();
       $sites =  Site::whereIn('status',[1,2])->orderBy('capacity','DESC')->get();
       $ids = [];
       $site_id_name = [];
       $gen_cap = [];
       $online = [];
       $site_zero = [];
        $str = "<table border=0><tr><td colspan='4' align='center'>".date('Y-m-d H:i:s')."</td><td>导出数据</td></tr><tr><th align='center'>区域</th><th align='center'>电站名称</th><th>容量（MW）</th><th>日发电量(kwh)</th><th>总台数/在线台数</th></tr>";
       foreach ($sites as $site){
           if($site) {
               array_push($ids, $site->id);
               array_push($site_id_name,['site_id'=>$site->id,'name'=>$site->name,'capacity'=>$site->capacity,'zone'=>$site->zone,'status'=>$site->status,'equipment_num'=>$site->equipment_num]);
           }
       }
       $site_name = collect($site_id_name)->keyBy('site_id')->all();
        foreach ($ids as $id) {
            $count = 0;
            $key = 'xy:industry:data:' . $id . ':*';
            $keys = $redis->keys($key);
            $pipe = $redis->multi(Redis::PIPELINE);
            foreach ($keys as $k) {
                $pipe->get($k);
            }
            $results = $pipe->exec();
            $data = [];
            $lost_data = [];
            $zero = [];
            foreach ($results as $result) {
                if ($result) {
                    $arr = json_decode($result, true);
                    if (array_key_exists("created_at",$arr)){
                            $st = strtotime($arr['created_at']);
                    }else{
                        $st = strtotime($arr['EventTime']);
                    }
                    if (date('Y-m-d',$st ) == date('Y-m-d')) {
                        if(time() - $st < 3600 ) {
                            if($arr['DayGenCap'] >= 0 && $arr['DayGenCap']<10000 ) {
                                array_push($data, $arr['DayGenCap']);
                                $count++;
                                if(intval($arr['ActivePower']) < 50){
                                    array_push($zero,$arr['device_id']);
                                }
                            }
                        }else{
                            array_push($lost_data,$arr['DayGenCap']);
                        }
                    }
                }
            }
            $value = collect($data)->sum();
            $lost_value = collect($lost_data)->sum();
            if($value > 0){
                $gen_cap[$id] = $value;
            }else{
                $gen_cap[$id] = $lost_value;
            }
             if(count($zero)>0){
                asort($zero);
            }else{
                 $zero = [];
             }
            $site_zero[$id] = $zero;
            $online[$id] = $count;
        }
//           $str = $site_name[$id]['zone'].'      '.$site_name[$id]['name'].'      '.$site_name[$id]['capacity'].'     '.$value.'<br>';
        $str2 = "<tr><td colspan='4' align='center'>代理运维电站</td></tr>";
        foreach ($site_name as $id=>$v) {
            $zone_str = "";
            $count = 1;
            if(count($site_zero[$id])>0) {
                $site_zero_status = 1;
                foreach ($site_zero[$id] as $s) {
                    if ($count % 20 == 0) {
                        $zone_str .= $s . ',<br>';
                    } else {
                        $zone_str .= $s . ',';
                    }
                    $count++;
                }
                $zone_str = substr($zone_str,0,-1);
            }else{
                $zone_str = "";
                $site_zero_status = 0;
            }

            if($v) {
                if($v['status'] == 1) {
                    if($site_name[$id]['equipment_num'] == $online[$id]) {
                        $str .= "<tr>
                                <td align='center'>" . $site_name[$id]['zone'] . "</td>
                                <td  align='center'>" . $site_name[$id]['name'] . "</td>
                                <td  align='center'>" . $site_name[$id]['capacity'] . "</td>
                                <td  align='center'>" . $gen_cap[$id] . "</td>
                                <td  align='center'>" . $site_name[$id]['equipment_num'] . '/' . $online[$id] . "</td>
                               ";
                        if($site_zero_status == 1) {
                            $str .= "</tr><tr><td colspan='2'><font color='red'>当前功率是0逆变器编号</font></td><td colspan='4' align='center'>$zone_str</td></tr>";
                        }else{
                            $str .= "</tr>";
                        }

                    }else{
                        $str .= "<tr>
                                <td align='center'>" . $site_name[$id]['zone'] . "</td>
                                <td  align='center'>" . $site_name[$id]['name'] . "</td>
                                <td  align='center'>" . $site_name[$id]['capacity'] . "</td>
                                <td  align='center'>" . $gen_cap[$id] . "</td>
                                <td  align='center'>
                                <font color='red'>" . $site_name[$id]['equipment_num'] . '/' . $online[$id] . "</font></td>
                                ";
                        if($site_zero_status == 1) {
                            $str .= "</tr><tr><td colspan='2'><font color='red'>当前功率为0的逆变器编号</font></td><td colspan='4' align='center'>$zone_str</td></tr>";
                        }else{
                            $str .= "</tr>";
                        }
                    }
                }else{
                    if($site_name[$id]['equipment_num'] == $online[$id]) {
                        $str2 .= "<tr>
                                  <td align='center'>" . $site_name[$id]['zone'] . "</td>
                                  <td  align='center'>" . $site_name[$id]['name'] . "</td>
                                  <td  align='center'>" . $site_name[$id]['capacity'] . "</td>
                                  <td  align='center'>" . $gen_cap[$id] . "</td>
                                  <td  align='center'>" . $site_name[$id]['equipment_num'] . '/' . $online[$id] . "</td>
                                  ";
                        if($site_zero_status == 1) {
                            $str2 .= "</tr><tr><td colspan='2'><font color='red'>当前功率为0的逆变器编号</font></td><td colspan='4' align='center'>$zone_str</td></tr>";
                        }else{
                            $str2 .= "</tr>";
                        }
                    }else{
                        $str2 .= "<tr>
                                  <td align='center'>" . $site_name[$id]['zone'] . "</td>
                                  <td  align='center'>" . $site_name[$id]['name'] . "</td>
                                  <td  align='center'>" . $site_name[$id]['capacity'] . "</td>
                                  <td  align='center'>" . $gen_cap[$id] . "</td>
                                  <td  align='center'>
                                  <font color='red'>" . $site_name[$id]['equipment_num'] . '/' . $online[$id] . "</font>
                                  </td>
                                   ";
                        if($site_zero_status == 1) {
                            $str2 .= "</tr><tr><td colspan='2'><font color='red'>当前功率为0的逆变器编号</font></td><td colspan='4' align='center'>$zone_str</td></tr>";
                        }else{
                            $str2 .= "</tr>";
                        }
                    }
                }
            }
        }
//           echo $str;



        $str=$str.$str2."</table>";
        printf("%s",$str);
    }

//    public function getTotalGenCap(Request $request)
//    {
//        $all = 0;
//        for ($i = 1; $i <= 29; $i++) {
//            $t = InverterData::getTotalGenCapByDeviceId($i);
//            InverterData::getYearGenCapByDeviceId($i);
//            //dump($t);
//            $all = $all + $t;
//        }
//        dump('All: ' . $all);
//        return '';
//
//    }

    //显示页面
    public function getExportList(){
        $sites = Site::whereIn('status',['1','2'])->orderBy('status')->get();
        return view('export',['sites'=>$sites]);
    }

    //导出代码
    public function getExportData(Request $request){
        $table = ['one','two','three','four','five','six','seven','eight',
            'nine','ten','eleven','twelve','thirteen','fourteen', 'fifteen','sixteen','seventeen','eighteen','nineteen', 'twenty','twentyone','twentytwo','twentythree','twentyfour','twentyfive', 'twentysix','twentyseven','twentyeight', 'twentynine', 'thirty','thirtyone'];
        $val = $request->input('date');
        $site_ids = explode(',',$val);
//        $month = date('n');
//        $month = 6;
        $uyear = $request->input('year');
        $umonth = $request->input('month');
        if (is_null($uyear)){
            $year = date('Y');
        }else{
            $year = $uyear;
        }
        if (is_null($umonth)){
            $month = date('m');
        }else{
            $month = $umonth;
        }

        $result = [];

        $sites = Site::whereIn('id',$site_ids)->get(['id','name','equipment_num']);
        $sites = $sites->toArray();
        $id_site = collect($sites)->keyBy('id');

        if ($month == date("m")) {
            $day_line = date('d');
        }else{
            $day_line = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        }
//        $day_line = 31;
//        $month = date('m');
//        $month = 7;
        foreach ($site_ids as $site_id) {
            $lists = [];

            $device_num = $id_site[$site_id]['equipment_num'];
            $header_row = ['设备号'];
            for($i =1;$i<=$device_num;$i++){
                array_push($header_row,$i);
            }
            array_push($header_row,'总计');
            array_push($lists,$header_row);
            $objs = RecordDevicesDayGenCap::where('site_id', $site_id)->where('month', $month)->where('year', $year)->orderBy('device_id')->get();
            $end_row = [$month.'月份总计'];


                foreach ($objs as $obj){
//                $end_index = $i;
                    $device_sum = 0;
                    for ($i = 1; $i <= $day_line ; $i++) {
                        $index = $i - 1;
                        $day = $table[$index];
                        $device_sum += $obj->$day;
                    }
                     $end_row[$obj->device_id] = $device_sum;
                }

//            $lists = $objs->toArray();
            //表格row数据
            for ($i = 1;$i <= $day_line ;$i++){
                $day_z = $i.'日';
                $index = $i - 1;
                $day = $table[$index];
                $row = [];
                $row = array_pad($row,$device_num,0);

                foreach ($objs as $list){
                   $gen = $list->$day;
                   $device_id = $list->device_id - 1;
                   $row[$device_id] = $gen;
                }
                $sum = array_sum($row);
                array_push($row,$sum);
                array_unshift($row,$day_z);
                array_push($lists,$row);
            }

            array_push($lists,$end_row);
            $result[$site_id]= $lists;
        }
//        $str = date('Y-m-d');
        $str = $year.'.'.$month;
//        dump($result);
//        dump($id_site);
//        $str = '2019-06-30';
        Excel::create($str.'发电数据',function($excel)use($result,$id_site) {
            foreach ($result as $key=>$param) {
                    $name =  $id_site[$key]['name'];
                    $excel->sheet($name, function ($sheet) use ($param) {
                        $sheet->rows($param);
                    });
            }
        })->download('xls');
    }


    public function setRecordDataByMysql(Request $request){
//        $begin = $request->input('begin');
//        $begin = '2019-04-02 23:59:59';
//        $begin = date('Y-m-d').'23:59:59';
        $table = ['one','two','three','four','five','six','seven','eight',
            'nine','ten','eleven','twelve','thirteen','fourteen', 'fifteen','sixteen','seventeen','eighteen','nineteen', 'twenty','twentyone','twentytwo','twentythree','twentyfour','twentyfive', 'twentysix','twentyseven','twentyeight', 'twentynine', 'thirty','thirtyone'];

//        $month = date('m');
        $year  = date('Y');
//        $sites =  Site::whereIn('status',[1,2])->get();
        $sites = Site::whereIn('id',[33])->get();
//        $filed = intval(date('d',strtotime($begin))) -1;
        foreach ($sites as $site) {
//        $fileds = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23];
            $fileds = [24,25,26,27,28,29,30,31];
          foreach ( $fileds as $filed) {
              $day = $filed ;
            if ($filed <= 9) {
//                $begin = date('Y-m-').'0'.$day.' 23:59:59';
                $begin = '2020-04-0'.$day.' 23:59:59';
             }else{
//                $begin = date('Y-m-').$day.' 23:59:59';
                $begin = '2020-04-'.$day.' 23:59:59';
            }
            $key = $filed -1;
            $index = $table[$key];
                $id = $site->id;
//                $devices = Device::where('site_id', $id)->get(['device_id']);
              $devices = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28];
                $results = [];
                switch ($id) {
                    case $id < 18:
                        foreach ($devices as $device) {
//                            $device_id = $device->device_id;
                            $device_id = $device;
                            $obj = InverterData::where('site_id', $site->id)->where('device_id', $device_id)->where('DayGenCap','<>',0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();
                            if ($obj) {
                                $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                            } else {
                                $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => 0];
                            }
                            array_push($results, $device_info);
                        }
                        break;
                    case $id >= 18 && $id < 43:
                        foreach ($devices as $device) {
//                            $device_id = $device->device_id;
                            $device_id = $device;
                            $obj = InverterDataTwo::where('site_id', $site->id)->where('device_id', $device_id)->where('DayGenCap','<>',0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();
                            if ($obj) {
                                $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                            } else {
                                $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => 0];
                            }
                            array_push($results, $device_info);
                        }
                        break;
                    default:
                        foreach ($devices as $device) {
                            $device_id = $device->device_id;
                            $obj = InverterDataThree::where('site_id', $site->id)->where('device_id', $device_id)->where('DayGenCap','<>',0)->where('EventTime', '<', $begin)->orderBy('id', 'DESC')->first();

                            if ($obj) {
                                $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => $obj->DayGenCap];
                            } else {
                                $device_info = ['site_id' => $site->id, 'device_id' => $device_id, $index => 0];
                            }
                            array_push($results, $device_info);
                        }
                }

                foreach ($results as $result) {
                    $param['site_id'] = $result['site_id'];
                    $param['device_id'] = $result['device_id'];
                    $param[$index] = $result[$index];
                    $param['month'] = 4;
                    $param['year'] = $year;

                    $record = RecordDevicesDayGenCap::where('site_id', $result['site_id'])
                        ->where('device_id', $result['device_id'])
                        ->where('month', 4)
                        ->where('year', $year)
                        ->first();
                    if ($record) {
//                        if($record->$index ==0) {
                            $record->update($param);
//                        }
                    } else {
                        RecordDevicesDayGenCap::create($param);
                    }
                    unset($param);
                }
            }
        }
    }

    //记录每天
    public function setRecordData(){
        $table = ['one','two','three','four','five','six','seven','eight',
            'nine','ten','eleven','twelve','thirteen','fourteen', 'fifteen','sixteen','seventeen','eighteen','nineteen', 'twenty','twentyone','twentytwo','twentythree','twentyfour','twentyfive', 'twentysix','twentyseven','twentyeight', 'twentynine', 'thirty','thirtyone'];
       $sites =  Site::whereIn('status',[1,2])->get();
       $redis = $this->_Redis();
       foreach ($sites as $site){
           $id = $site->id;
           $key = 'xy:industry:data:' . $id . ':*';
           $keys = $redis->keys($key);
           $pipe = $redis->multi(Redis::PIPELINE);
           foreach ($keys as $k) {
               $pipe->get($k);
           }
           $results = $pipe->exec();
           foreach ($results as $result){
               $obj = json_decode($result);
               $day_gen = $obj->DayGenCap;
               $month = date('m');
               $year = date('Y');
               $index = intval(date('d')) -1;
               $day = $table[$index];
               $date= date('Y-m-d',strtotime($obj->EventTime));
               if($date == date('Y-m-d')) {
                   $device_id = $obj->device_id;
                   if ($day_gen > 0 && $day_gen < 2000) {
                       $param[$day] = $day_gen;
                   } else {
                       $param[$day] = 0;
                   }
                   $param['site_id'] = $obj->site_id;
                   $param['device_id'] = $device_id;
                   $param['month'] = $month;
                   $param['year'] = $year;

                   $obj = RecordDevicesDayGenCap::where('site_id', $id)
                       ->where('device_id', $device_id)
                       ->where('month', $month)
                       ->where('year',$year)
                       ->first();
                   if ($obj) {
                       if($obj->$day == 0) {
                           $obj->update($param);
                       }
                   }else{
                       RecordDevicesDayGenCap::create($param);
                   }
               }

           }
       }
    }

    public function getStatistic(Request $request)
    {
        $date = '2016-04-29';
        $this->executeStatisticsDayEnergy($date);

        dump('success');
    }

    private function executeStatisticsDayEnergy($date)
    {
        $sites = Site::get();

        foreach ($sites as $site) {
            $sde = StatisticsDayEnergy::getOneDayBySiteId($site->id, $date);
            if (!$sde) {
                $sde = new StatisticsDayEnergy();
                $sde->site_id = $site->id;
                $sde->user_id = $site->user_id;
                $sde->date = $date;
                $sde->site_value = InverterData::getOneDayGenCapBySiteId($site->id, $date);
                $sde->save();
            }
            $sdes = StatisticsDayEnergy::getOneDayByUserId($site->user_id, $date);
            $user_value = 0;
            foreach ($sdes as $s) {
                $user_value = $user_value + $s->site_value;
            }
            foreach ($sdes as $s) {
                $s->user_value = $user_value;
                $s->save();
            }

        }
    }

    public function getTotal()
    {
//        $date = date('Y-m-d');//获取当天
        $date = '2018-02-26';
        $sites = Site::get();
        foreach ($sites as $site) {
            $sde = StatisticsDayEnergy::getOneDayBySiteId($site->id, $date);
            if (!$sde) {
                $sde = new StatisticsDayEnergy();
                $sde->site_id = $site->id;
                $sde->user_id = $site->user_id;
                $sde->date = $date;
                if($site->id<18) {
                    $sde->site_value = InverterData::getOneDayGenCapBySiteId($site->id, $date);
                }else{
                    $sde->site_value = InverterDataTwo::getOneDayGenCapBySiteId($site->id, $date);
                }
                $sde->save();

                $sdes = StatisticsDayEnergy::getOneDayByUserId($site->user_id, $date);
                $user_value = 0;
                foreach ($sdes as $s) {
                    $user_value = $user_value + $s->site_value;
                }
                foreach ($sdes as $s) {
                    $s->user_value = $user_value;
                    $s->save();
                }
            }


        }

//        $date = date('Y-m-d');//获取当天
//        $date = '2017-08-30';
        $devices = Device::get();
        foreach ($devices as $device) {
            $sdde = StatisticsDeviceDayEnergy::getOneDayByDeviceId($device->user_id,$device->device_id, $date);
            if (!$sdde) {
                $sdde = new StatisticsDeviceDayEnergy();
                $sdde->device_id = $device->device_id;
                $sdde->user_id = $device->user_id;
                $sdde->site_id = $device->site_id;
                $sdde->date = $date;
                if($device->user_id < 17) {
                    $sdde->value = InverterData::getOneDayGenCapByDeviceId($device->user_id, $device->device_id, $date);
                }else{
                    $sdde->value = InverterDataTwo::getOneDayGenCapByDeviceId($device->user_id, $device->device_id, $date);
                }
                $sdde->save();
            }
        }
    }

    public function getStatisticDevice(Request $request)
    {


////        $begin_time = date('Y-m-d') . ' 0:0:0';
        $begin_time = '2017-12-31 16:00:00';
////        $end_time = date('Y-m-d') . ' 23:59:59';
        $end_time = '2017-12-31 19:59:59';
//
        $objs = InverterData::where('site_id',1)->whereBetween('EventTime', [$begin_time, $end_time])->groupBy('device_id')->max('TotGenCap');
//        dump($objs);

         $objs = DB::table('inverter_datas_three')->select(DB::raw('max(TotGenCap) as value,device_id'))->groupBy('device_id')->get();
        dump($objs);
//        foreach ($objs as $obj){
//            dump($obj);
//        }
//        $devices = Device::where('site_id',2)->get();
//        foreach ($devices  as $device) {
//            if($device->site_id < 18) {
//                $data = InverterData::where('device_id', $device->id)
//                    ->where('site_id',$device->site_id)
//                    ->whereBetween('EventTime', [$begin_time, $end_time])
//                    ->orderBy('id', 'desc')->first();
//            }else{
//                $data = InverterDataTwo::where('device_id', $device->id)
//                    ->where('site_id',$device->site_id)
//                    ->whereBetween('EventTime', [$begin_time, $end_time])
//                    ->orderBy('id', 'desc')->first();
//            }
//            if($data) {
//                $obj = StatisticsDeviceYearEnergy::where('site_id',$device->site_id)->where('device_id',$device->id)->where('date',2017)->first();
//                if(!$obj) {
//                    StatisticsDeviceYearEnergy::create(['site_id' => $device->site_id, 'user_id' => $device->user_id, 'device_id' => $device->device_id, 'value' => $data->TotGenCap, 'date' => 2017]);
//                }
//            }
//        }
    }

    protected function _Redis()
    {
        $redis=new Redis();
        $redis->connect(config('app.redishost'),config('app.redisport'));
        $redis->auth(config('app.redispwd'));
        $redis->select(1);
//        $redis->connect('120.26.220.134',config('app.redispost'));
//        $redis->auth(config('app.redispwd'));
//        $redis->auth('lggtest');
        return $redis;
    }


    protected function _MyRedis($num)
    {
        $redis=new Redis();
        $redis->connect(config('app.redishost'),config('app.redisport'));
        $redis->auth(config('app.redispwd'));
        $redis->select($num);
//        $redis->connect('120.26.220.134',config('app.redispost'));
//        $redis->auth(config('app.redispwd'));
//        $redis->auth('lggtest');
        return $redis;
    }


    public function postDataOffset(Request $request){
        if (!$request->has('site_id')){
            dump("失败");
            return;
        }
        if (!$request->has('ids')){
            dump("失败");
            return;
        }
        $site_id = $request->input('site_id');
        $ids = $request->input('ids');
        dump($ids);
        $redis = $this->_Redis();
        $arrs = explode(',',$ids);
        switch ($site_id){
            case $site_id < 18:
                foreach ($arrs as $id){
                  $obj = InverterData::find($id);
                  $device_id = $obj->device_id;
                  $str = json_encode($obj);
                  dump($str);
                  $redis->set("xy:industryfv:offset:".$site_id.":".$device_id,$str);
                }
                break;
            case $site_id >=18 && $site_id<43:
                foreach ($arrs as $id){
                    $obj = InverterDataTwo::find($id);
                    $device_id = $obj->device_id;
                    $str = json_encode($obj);
                    dump($str);
                    $redis->set("xy:industryfv:offset:".$site_id.":".$device_id,$str);
                }
                break;
            default:
                foreach ($arrs as $id){
                    $obj = InverterDataThree::find($id);
                    $device_id = $obj->device_id;
                    $str = json_encode($obj);
                    dump($str);
                    $redis->set("xy:industryfv:offset:".$site_id.":".$device_id,$str);
                }
        }

    }



//    private function executeStatisticsDeviceDayEnergy($date)
//    {
//        $devices = Device::get();
//
//        foreach ($devices as $device) {
//            $sdde = StatisticsDeviceDayEnergy::getOneDayByDeviceId($device->device_id, $date);
//            if (!$sdde) {
//                $sdde = new StatisticsDeviceDayEnergy();
//                $sdde->device_id = $device->device_id;
//                $sdde->user_id = $device->user_id;
//                $sdde->site_id = $device->site_id;
//                $sdde->date = $date;
//                $sdde->value = InverterData::getOneDayGenCapByDeviceId($device->device_id, $date);
//                $sdde->save();
//            }
//        }
//    }

//    public function getTest()
//    {
//
//        $year_gen_cap = InverterData::getYearGenCapByUserId(2);
//        return $year_gen_cap;
//    }


    public function getDelAll(){

        $redis = $this->_MyRedis(2);
        $key = 'xinyun:rcv:data:*';
        $keys = $redis->keys($key);
        $pipe = $redis->multi(Redis::PIPELINE);
        foreach ($keys as $k) {
            $pipe->del($k);
        }
        $results = $pipe->exec();
        dump($results);
    }



}

